﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Utility.Helper;

namespace BLL
{
    public class YX_BANK_LISTBLL : BaseBLL
    {
        /// <summary>
        /// 根据编号查询银行流水明细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YX_BANK_DETAILLIST SelectById(string id)
        {
            string sql = string.Format(@"select * from YX_BANK_DETAILLIST a where a.N_DETAIL_LISTID= " + id);

            DataTable dt = Query(sql);

            YX_BANK_DETAILLIST model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = new YX_BANK_DETAILLIST();
                    model.N_DETAIL_LISTID = Convert.ToDecimal(id);
                    model.N_LIST_ID = decimal.Parse(dr["N_LIST_ID"].ToString());
                    model.D_TRANDATE = Convert.ToDateTime(dr["D_TRANDATE"].ToString());
                    model.C_CRE_TYP = dr["C_CRE_TYP"].ToString();
                    model.C_CRE_NO = dr["C_CRE_NO"].ToString();
                    model.C_MESSAGE = dr["C_MESSAGE"].ToString();
                    model.N_AMT = Convert.ToDecimal(dr["N_AMT"].ToString());
                    model.N_AMT1 = Convert.ToDecimal(dr["N_AMT1"].ToString());
                    model.N_FLAG1 = Convert.ToDecimal(dr["N_FLAG1"].ToString());
                    model.C_ACCNO2 = dr["C_ACCNO2"].ToString();
                    model.C_ACC_NAME1 = dr["C_ACC_NAME1"].ToString();
                    model.N_FLAG2 = dr.GetValue<decimal>("N_FLAG2");
                    model.C_TRAN_FLOW = dr["C_TRAN_FLOW"].ToString();
                    model.C_BFLOW = dr["C_BFLOW"].ToString();
                    model.C_DET_NO = dr["C_DET_NO"].ToString();
                    model.C_DET = dr["C_DET"].ToString();
                    model.C_RLTV_ACCNO = dr["C_RLTV_ACCNO"].ToString();
                    model.C_CADBANK_NM = dr["C_CADBANK_NM"].ToString();

                    model.C_PROWEEKNUM = dr["C_PROWEEKNUM"].ToString();
                    model.C_ACCNO1 = dr["C_ACCNO1"].ToString();
                    model.N_BANK_ID = Convert.ToDecimal(dr["N_BANK_ID"].ToString());

                    model.N_STATE = Convert.ToDecimal(dr["N_STATE"].ToString());
                }
            }

            return model;
        }

        public int INSERT(YX_BANK_DETAILLIST model, string operName)
        {
            model.N_DETAIL_LISTID = GetSequence("SEQ_DETAIL_LISTID");
            string sql = string.Format(@"insert into yx_bank_detaillist
  (n_detail_listid,
   n_list_id,
   d_trandate,
   c_cre_typ,
   c_cre_no,
   c_message,
   n_amt,
   n_amt1,
   n_flag1,
   c_accno2,
   c_acc_name1,
   n_flag2,
   c_tran_flow,
   c_bflow,
   c_det_no,
   c_det,
   c_rltv_accno,
   c_cadbank_nm,
   c_proweeknum,
   c_accno1,
   n_bank_id,D_BANKREDATE,N_COMPANY_ID)
values
  ({0},
   {1},
   TO_DATE('{2}','YYYY-MM-DD'),
   '',
   '',
   '',
   {3},
   {4},
   {5},
   '{6}',
   '{7}',
   0,
   '',
   '',
   '',
   '{8}',
   '',
   '',
   '{9}',
   '{10}',
   {11}, TO_DATE('{12}','YYYY-MM-DD'),{13})", model.N_DETAIL_LISTID, model.N_LIST_ID, model.D_TRANDATE.ToString("yyyy-MM-dd"), model.N_AMT, model.N_AMT1, model.N_FLAG1, model.C_ACCNO2, model.C_ACC_NAME1, model.C_DET, model.C_PROWEEKNUM, model.C_ACCNO1, model.N_BANK_ID, model.D_BANKREDATE.ToString("yyyy-MM-dd"), model.N_COMPANY_ID);

            int count = NoQuery(sql, "新增流水", "INSERT", operName);
            return count;
        }

        public int UPDATE(YX_BANK_DETAILLIST model, string operName)
        {
            string sql = @"update yx_bank_detaillist
        set
       d_trandate      = TO_DATE('" + model.D_TRANDATE.ToString("yyyy-MM-dd") + @"','YYYY-MM-DD'),

       D_BANKREDATE      = TO_DATE('" + model.D_BANKREDATE.ToString("yyyy-MM-dd") + @"','YYYY-MM-DD'),
       n_amt           = " + model.N_AMT + @",
       n_flag1         = " + model.N_FLAG1 + @",
       c_accno2        = '" + model.C_ACCNO2 + @"',
       c_acc_name1     = '" + model.C_ACC_NAME1 + @"',

       c_det           = '" + model.C_DET + @"',

       c_proweeknum    = '" + model.C_PROWEEKNUM + @"',
       c_accno1        = '" + model.C_ACCNO1 + @"',
       n_bank_id       = " + model.N_BANK_ID + @"
 where n_detail_listid = " + model.N_DETAIL_LISTID;
            int count = NoQuery(sql, operName);
            return count;
        }

        public int UPDATE2(YX_BANK_DETAILLIST model, string operName)
        {
            string sql = @"update yx_bank_detaillist
        set
       d_trandate      = TO_DATE('" + model.D_TRANDATE.ToString("yyyy-MM-dd") + @"','YYYY-MM-DD'),
       c_det           = '" + model.C_DET + @"'

 where n_detail_listid = " + model.N_DETAIL_LISTID;
            int count = NoQuery(sql, operName);

            if (count > 0)
            {
                YX_BANK_DETAILLIST nModel = SelectById(model.N_DETAIL_LISTID.ToString());

                if (nModel.N_STATE == 1)
                {
                    //已经匹配过，重新更新

                    ProUpdateBankSet(model.N_DETAIL_LISTID.ToString());
                }
                else
                {
                    ProBankSet();
                }
            }
            return count;
        }

        #region 周

        public int GetRecordCount(string whereStr, string operName)
        {
            var sql = @"SELECT
  COUNT(1)
FROM YX_BANK_DETAILLIST T1
--LEFT JOIN YX_CUSTOMER_BANK T2 ON T1.C_ACCNO2 = T2.C_NUMBER AND T1.C_ACCNO2 IS NOT NULL
LEFT JOIN YX_CUSTOMER T3 ON T1.C_ACC_NAME1 = T3.C_NAME
LEFT JOIN QS_PLANWEEK_DETAIL T4 ON T1.N_WEEKDETAIL_ID = T4.N_WEEKDETAIL_ID AND T1.N_STATE = 1
LEFT JOIN QS_PLANWEEKAPPLY T5 ON T4.N_WEEKAPP_ID = T5.N_WEEKAPP_ID
LEFT JOIN YX_ACCOUNT T6 ON T1.C_ACCNO1 = T6.ACCOUNTNO WHERE 1=1";
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }
            int count = GetCount(sql);

            return count;
        }

        public int GetRecordCount1(string whereStr, string operName)
        {
            string sql = @"select COUNT(1)
  from yx_bank_list       t,
       yx_bank_detaillist d,
       sys_bank           b,
       yx_account         a,
       sys_company        c
 where t.n_list_id = d.n_list_id
   and d.n_bank_id = b.n_id
   and d.C_ACCNO1 = a.accountno
   and a.company_id = c.cmy_id ";

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            return GetCount(sql);
        }

        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r, a.*
          from (select d.n_detail_listid ,
       d.C_ACCNO1,
       d.c_accno2,
       d.c_acc_name1,
       b.c_name,
       c.cmy_name,
       to_char(d.d_trandate, 'yyyy-mm-dd') d_trandate,
    --   trunc(d.d_trandate) d_trandate,
       d.n_amt,
       d.n_amt1,
       d.C_PROWEEKNUM,
       decode(d.n_flag1, 0, '转出', '转入') n_flag1,
       d.C_MESSAGE,
       d.C_DET
  from yx_bank_list       t,
       yx_bank_detaillist d,
       sys_bank           b,
       yx_account         a,
       sys_company        c
 where t.n_list_id = d.n_list_id
   and d.n_bank_id = b.n_id
   and d.C_ACCNO1 = a.accountno
   and a.company_id = c.cmy_id ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @") a ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询银行流水", "");// DbHelperSQL.Query(sql);
        }

        #endregion 周

        #region 月

        public int GetRecordCountMonth(string whereStr, string operName)
        {
            string sql = string.Format(@"SELECT count(1)
  from yx_bank_list        t,
       yx_bank_detaillist  d,
       sys_bank            b,
       yx_account          a,
       sys_company         c,
       qs_planmonth_detail m,
       qs_planweekapply    wp,
       qs_planweek_detail  w
 where t.n_list_id = d.n_list_id
   and d.n_bank_id = b.n_id
   and d.C_ACCNO1 = a.accountno
   and a.company_id = c.cmy_id
   and wp.n_monthapp_id = m.n_monthapp_id
   and wp.n_weekapp_id = w.n_weekapp_id
   and w.n_proid = m.n_proid
   and w.n_weekdetail_id = d.n_weekdetail_id
   and w.n_proid = d.n_proid " + whereStr);

            int count = GetCount(sql);

            return count;
        }

        /// <summary>
        /// 获取月资金流水信息总额
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public decimal GetRecordPricceSum(string whereStr)
        {
            string sql = string.Format(@"SELECT sum(d.n_amt1)
  from yx_bank_list        t,
       yx_bank_detaillist  d,
       sys_bank            b,
       yx_account          a,
       sys_company         c,
       qs_planmonth_detail m,
       qs_planweekapply    wp,
       qs_planweek_detail  w
 where t.n_list_id = d.n_list_id
   and d.n_bank_id = b.n_id
   and d.C_ACCNO1 = a.accountno
   and a.company_id = c.cmy_id
   and wp.n_monthapp_id = m.n_monthapp_id
   and wp.n_weekapp_id = w.n_weekapp_id
   and w.n_proid = m.n_proid
   and w.n_weekdetail_id = d.n_weekdetail_id
   and w.n_proid = d.n_proid " + whereStr);
            var dt = Query(sql);
            if (dt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                if (dt.Rows[0][0] == DBNull.Value)
                {
                    return 0;
                }
                return Convert.ToDecimal(dt.Rows[0][0]);
            }
        }

        public DataTable GetListByPageMonth(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT *
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
               d.n_detail_listid,
               d.C_ACCNO1,
               d.c_accno2,
               d.c_acc_name1,
               b.c_name,
               c.cmy_name,
               to_char(d.d_trandate, 'yyyy-mm-dd') d_trandate,
               --   trunc(d.d_trandate) d_trandate,
               d.n_amt,
               d.n_amt1,
               d.C_PROWEEKNUM,
               decode(d.n_flag1, 0, '转出', '转入') n_flag1
          from yx_bank_list        t,
               yx_bank_detaillist  d,
               sys_bank            b,
               yx_account          a,
               sys_company         c,
               qs_planmonth_detail m,
               qs_planweekapply    wp,
               qs_planweek_detail  w
         where t.n_list_id = d.n_list_id
           and d.n_bank_id = b.n_id
           and d.C_ACCNO1 = a.accountno
           and a.company_id = c.cmy_id
           and wp.n_monthapp_id = m.n_monthapp_id
           and wp.n_weekapp_id = w.n_weekapp_id
           and w.n_proid = m.n_proid
           and w.n_weekdetail_id = d.n_weekdetail_id
           and w.n_proid = d.n_proid
           and d.n_state=1");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");// DbHelperSQL.Query(sql);
        }

        #endregion 月

        #region 年

        public int GetRecordCountYear(string whereStr, string operName)
        {
            string sql = string.Format(@"SELECT count(1)
  from yx_bank_list        t,
       yx_bank_detaillist  d,
       sys_bank            b,
       yx_account          a,
       sys_company         c,
       qs_planmonth_detail m,
       qs_planweekapply    wp,
       qs_planweek_detail  w
 where t.n_list_id = d.n_list_id
   and d.n_bank_id = b.n_id
   and d.C_ACCNO1 = a.accountno
   and a.company_id = c.cmy_id
   and wp.n_monthapp_id = m.n_monthapp_id
   and wp.n_weekapp_id = w.n_weekapp_id
   and w.n_proid = m.n_proid
   and w.n_weekdetail_id = d.n_weekdetail_id
   and w.n_proid = d.n_proid " + whereStr);

            int count = GetCount(sql);

            return count;
        }

        public DataTable GetListByPageYear(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT *
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
               d.n_detail_listid,
               d.C_ACCNO1,
               d.c_accno2,
               d.c_acc_name1,
               b.c_name,
               c.cmy_name,
               to_char(d.d_trandate, 'yyyy-mm-dd') d_trandate,
               --   trunc(d.d_trandate) d_trandate,
               d.n_amt,
               d.n_amt1,
               d.C_PROWEEKNUM,
               decode(d.n_flag1, 0, '转出', '转入') n_flag1
          from yx_bank_list        t,
               yx_bank_detaillist  d,
               sys_bank            b,
               yx_account          a,
               sys_company         c,
               qs_planmonth_detail m,
               qs_planweekapply    wp,
               qs_planweek_detail  w
         where t.n_list_id = d.n_list_id
           and d.n_bank_id = b.n_id
           and d.C_ACCNO1 = a.accountno
           and a.company_id = c.cmy_id
           and wp.n_monthapp_id = m.n_monthapp_id
           and wp.n_weekapp_id = w.n_weekapp_id
           and w.n_proid = m.n_proid
           and w.n_weekdetail_id = d.n_weekdetail_id
           and w.n_proid = d.n_proid ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");// DbHelperSQL.Query(sql);
        }

        #endregion 年

        public DataTable GetListByPage2(string whereStr, string orderby, int startIndex, int endIndex)
        {
            var sql = @"SELECT
  ROW_NUMBER() OVER(ORDER BY " + orderby + @") as R,
  T1.*,
  T4.c_Proname AS PRO_NAME,
  T5.C_USEWEEK AS USE_WEEK,
  T3.C_NAME AS CUSTER_NAME,
CASE T3.N_CUSTOMER_TYPE
       WHEN 0 THEN '工业用户'
       WHEN 1 THEN '居民,商业,集体用户'
       WHEN 2 THEN '压缩天然气（CNG）'
       WHEN 3 THEN '液化天然气（LNG）'
       ELSE '其它'
  END AS CUSTOMER_TYPE,
  CASE
    WHEN T6.ACCOUNTTYPE = 1 AND T1.N_FLAG1 = 0 THEN '转出'
    WHEN T6.ACCOUNTTYPE = 2 AND T1.N_FLAG1 = 1 THEN '转入'
    ELSE '异常'
  END AS FLAG_MSG
FROM YX_BANK_DETAILLIST T1
--LEFT JOIN YX_CUSTOMER_BANK T2 ON T1.C_ACCNO2 = T2.C_NUMBER AND T1.C_ACCNO2 IS NOT NULL
LEFT JOIN YX_CUSTOMER T3 ON T1.C_ACC_NAME1 = T3.C_NAME
LEFT JOIN QS_PLANWEEK_DETAIL T4 ON T1.N_WEEKDETAIL_ID = T4.N_WEEKDETAIL_ID AND T1.N_STATE = 1
LEFT JOIN QS_PLANWEEKAPPLY T5 ON T4.N_WEEKAPP_ID = T5.N_WEEKAPP_ID
LEFT JOIN YX_ACCOUNT T6 ON T1.C_ACCNO1 = T6.ACCOUNTNO WHERE 1=1";
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }
            sql = string.Format("SELECT * FROM ({0}) T1 WHERE T1.R BETWEEN {1} AND {2}", sql, startIndex, endIndex);
            return Query(sql);
            /*
            string sql = string.Format(@"SELECT * FROM (select ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r, b.c_useweek, a.*
  from (SELECT d.n_detail_listid,
               d.C_ACCNO1,
               d.c_accno2,
               d.c_acc_name1 as dc_acc_name1,
               b.c_name,
               yc.c_name as yc_name,
               c.cmy_name,
               to_char(d.d_trandate, 'yyyy-mm-dd') d_trandate,
               d.n_amt,
               d.n_amt1,
               d.C_PROWEEKNUM,
               decode(d.n_state, 0, '未匹配', '已匹配') n_state,
               decode(d.n_flag1, 0, '转出', '转入') n_flag1,
               d.C_DET,
               d.n_proid,
               d.n_weekdetail_id
          from yx_bank_list       t,
               yx_bank_detaillist d,
               sys_bank           b,
               yx_account         a,
               sys_company        c,
               yx_customer        yc,
               yx_customer_bank   yb
          where t.n_list_id = d.n_list_id
           and yc.n_company_id = c.cmy_id
           and yc.n_id=yb.n_customer_id
           and yb.c_number=d.c_accno2
           and d.n_bank_id = b.n_id
           and d.C_ACCNO1 = a.accountno
           and a.company_id = c.cmy_id  ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @") TT";
            sql += " WHERE TT.r BETWEEN " + startIndex + " AND " + endIndex;
            return Query(sql, "", "GetListByPage", "查询队列", "");// DbHelperSQL.Query(sql);
            */
        }

        /// <summary>
        /// 银行流水未匹配查询
        /// </summary>
        /// <param name="company"></param>
        /// <param name="orderby"></param>
        /// <returns></returns>
        public DataTable ProGetUnmatch(string company, string where, string orderby)
        {
            string sql = "select * from YX_BANK_DETAILLIST where N_STATE = 0 ";

            if (!string.IsNullOrEmpty(company))
            {
                sql += " and N_COMPANY_ID in (select cmy_id from sys_company start with cmy_id = " + company + @" connect by prior cmy_id = cmy_parentid) ";
            }

            if (!string.IsNullOrEmpty(where))
            {
                sql += " and " + where;
            }

            if (!string.IsNullOrEmpty(orderby))
            {
                sql += " order by " + orderby;
            }

            return Query(sql, "", "ProGetUnmatch", "查询未匹配流水", "");
        }

        /// <summary>
        /// 自动匹配银行流水
        /// </summary>
        public void ProBankSet()
        {
            ServiceHelper myservice = new ServiceHelper();

            DataTable dt = new DataTable("orasptab");
            dt.Columns.Add("pname", Type.GetType("System.String"));
            dt.Columns.Add("pvalue", Type.GetType("System.String"));
            dt.Columns.Add("dir", Type.GetType("System.String"));
            dt.Columns.Add("size", Type.GetType("System.String"));

            //输出参数
            dt.Rows.Add(new object[] { "c_err", "", "0", 200 });

            string addstate = myservice.ExcuteProcEX("Pkg_Public_Method.Proc_BankList", dt, "Pkg_Public_Method", "Proc_BankList", "银行流水匹配", 0);
        }

        /// <summary>
        /// 重新匹配流水
        /// </summary>
        /// <param name="lists"></param>
        public void ProUpdateBankSet(string lists)
        {
            ServiceHelper myservice = new ServiceHelper();

            DataTable dt = new DataTable("orasptab");
            dt.Columns.Add("pname", Type.GetType("System.String"));
            dt.Columns.Add("pvalue", Type.GetType("System.String"));
            dt.Columns.Add("dir", Type.GetType("System.String"));
            dt.Columns.Add("size", Type.GetType("System.String"));

            dt.Rows.Add(new object[] { "c_lists", lists, "1", 100 });
            //输出参数
            dt.Rows.Add(new object[] { "c_err", "", "0", 200 });

            string addstate = myservice.ExcuteProcEX("Pkg_Public_Method.Proc_BankListUpdate", dt, "Pkg_Public_Method", "Proc_BankListUpdate", "更新银行流水", 1);
        }

        /// <summary>
        /// 明细报表条数
        /// </summary>
        /// <param name="whereStr"></param>
        /// <param name="operName"></param>
        /// <returns></returns>
        public int GetListReCount(out decimal amt, out decimal inje, out decimal outje, string whereStr, string operName)
        {
            string sql = string.Format(@"SELECT   count(1),
       nvL(SUM(T.N_AMT), 0),
       nvl(sum(decode(t.n_flag1, 1, t.n_amt, 0)), 0) intje,
       nvl(sum(decode(t.n_flag1, 0, t.n_amt, 0)), 0) outje
  from yx_bank_detaillist t, sys_bank b, sys_company c
 where t.n_bank_id = b.n_id
   and t.n_company_id = c.cmy_id " + whereStr);

            DataTable dt = Query(sql);
            int count = int.Parse(dt.Rows[0][0].ToString());
            amt = decimal.Parse(dt.Rows[0][1].ToString());

            inje = decimal.Parse(dt.Rows[0][2].ToString());
            outje = decimal.Parse(dt.Rows[0][3].ToString());
            return count;
        }

        /// <summary>
        /// 流水明细
        /// </summary>
        /// <param name="whereStr"></param>
        /// <param name="orderby"></param>
        /// <param name="startIndex"></param>
        /// <param name="endIndex"></param>
        /// <returns></returns>
        public DataTable GetListReByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
       c.cmy_name,
       t.C_ACCNO1,
       t.c_accno2,
       t.c_acc_name1,
       b.c_name,

       to_char(t.d_trandate, 'yyyy-mm-dd') d_trandate,
       t.n_amt,
       t.n_amt1,
       t.c_det,
       t.C_MESSAGE,
       decode(t.n_flag1, 0, '转出', '转入') n_flag1
  from yx_bank_detaillist t, sys_bank b, sys_company c
 where t.n_bank_id = b.n_id
   and t.n_company_id = c.cmy_id ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListReByPage", "查询银行流水明细", "");// DbHelperSQL.Query(sql);
        }

        public DataTable GetDataByApplyId(int applyId, int type, int proId)
        {
            //递归查询ProId

            var sql = @"SELECT
                            T1.*
                        FROM QS_PLANWEEK";
            return null;
        }

        public DataTable GetDataByApplyIdByWeek(int applyId, int proId)
        {
            //递归查询ProId

            var sql = @"SELECT
                            T1.*
                        FROM
                        (
                            SELECT
                              T1.*
                            FROM QS_PLANWEEK_DETAIL T1
                            WHERE
                              T1.N_WEEKAPP_ID = :WEEKAPP_ID
                        ) T1
                        START WITH T1.N_PROID = :PRO_ID
                        CONNECT BY PRIOR T1.N_PROID = T1.N_PARENTID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":WEEKAPP_ID", applyId, "int");
            parms.Add(":PRO_ID", proId, "int");
            var dt = Query(sql, parms);
            var arry = new List<int>();
            foreach (var item in dt.AsEnumerable())
            {
                arry.Add(Convert.ToInt32(item.GetValue<int>("N_PROID")));
            }
            sql = @"SELELCT
                        T2.C_ACC_NAME1,
                        T2.C_CADBANK_NM,
                        T2.C_ACCNO2,
                        T2.D_TRANDATE,
                        T2.N_AMT,
                        T2.T1.N_FLAG1
                        CASE T2.N_FLAG1
                            WHEN 0 THEN '转出'
                            ELSE '转入'
                        END AS 'C_FLAG',
                        T2.C_PROWEEKNUM,
                        T3.C_USEWEEK,
                        T1.C_PRONAME,
                        T2.C_DET
                    FROM QS_PLANWEEK_DETAIL T1
                    INNER JOIN YX_BANK_DETAILLIST T2 ON T1.N_WEEKDETAIL_ID = T2.N_WEEKDETAIL_ID AND T2.N_STATE = 1
                    INNER JOIN QS_PLANWEEKAPPLY T3 ON T3.N_WEEKAPP_ID = T1.N_WEEKAPP_ID";
            return null;
        }

        #region 总项目详情银行流水

        /// <summary>
        /// 根据年份,项目Id 分页查询
        /// </summary>
        public DataTable GetDataByPage(int year, int? month, int? week, int proId, int companyId, string orderByStr, int skip, int take)
        {
            var sql = @"SELECT
                          ROW_NUMBER() OVER(ORDER BY T5." + orderByStr + @") AS R,
                          T5.*
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T5.N_PROID = :PRO_ID AND
                          T1.N_YEAR = :YEAR";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":PRO_ID", proId, "int");
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            sql = "SELECT T1.* FROM (" + sql + ") T1 WHERE T1.R>:START_INDEX AND T1.R<= :END_INDEX";
            parms.Add(":START_INDEX", skip, "int");
            parms.Add(":END_INDEX", skip + take, "int");
            return Query(sql, parms);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public int GetDataByCount(int year, int? month, int? week, int proId, int companyId)
        {
            var sql = @"SELECT
                          COUNT(1) AS R
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T5.N_PROID = :PRO_ID AND
                          T1.N_YEAR = :YEAR";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":PRO_ID", proId, "int");
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            var dt = Query(sql, parms);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        /// <summary>
        /// 根据年份 项目Id 查询总流水金额
        /// </summary>
        public decimal GetDataBySum(int year, int? month, int? week, int proId, int companyId)
        {
            var sql = @"SELECT
                          ROUND(SUM(T5.N_AMT)/10000,6) AS AMT
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T5.N_PROID = :PRO_ID AND
                          T1.N_YEAR = :YEAR";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":PRO_ID", proId, "int");
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                    //parms.Add(":ZWEEK_INDEX ", week, "int");
                }
            }
            var dt = Query(sql, parms);
            if (dt.Rows[0][0] == DBNull.Value)
            {
                return 0;
            }
            return Convert.ToDecimal(dt.Rows[0][0]);
        }

        #endregion 总项目详情银行流水

        #region 子项目详情银行流水

        /// <summary>
        /// 根据年份,项目Id 分页查询
        /// </summary>
        public DataTable GetDataByPageDetail(int year, int? month, int? week, int proId, int companyId, string orderByStr, int skip, int take, int? nId, string DetailsCode, int? PLANAPP_ID)
        {
            var qs_plan_datail = "";
            var n_id = "";
            if (DetailsCode == "T1")
            {
                qs_plan_datail = "QS_PLAN_DETAIL1";
                n_id = "N_ID1";
            }
            else if (DetailsCode == "T2")
            {
                qs_plan_datail = "QS_PLAN_DETAIL2";
                n_id = "N_ID2";
            }
            else
            {
                qs_plan_datail = "QS_PLAN_DETAIL3";
                n_id = "N_ID3";
            }
            var sql = @"SELECT
                          ROW_NUMBER() OVER(ORDER BY T5." + orderByStr + @") AS R,
                          T5.*
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID
                        INNER JOIN " + qs_plan_datail + @" T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T4.N_IF_DETAILS = 1 AND
                          T4.C_DETAILS_CODE = 'T1' AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T5.N_PROID = :PRO_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6." + n_id + @"=:nId AND
                          T4.C_DETAILS_CODE=:DetailsCode AND
                          T6.N_PLANAPP_ID=:PLANAPP_ID";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":PRO_ID", proId, "int");
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", companyId, "int");

            parms.Add(":nId", nId, "int");
            parms.Add(":DetailsCode", DetailsCode, "string");
            parms.Add(":PLANAPP_ID", PLANAPP_ID, "int");
            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            sql = "SELECT T1.* FROM (" + sql + ") T1 WHERE T1.R>:START_INDEX AND T1.R<= :END_INDEX";
            parms.Add(":START_INDEX", skip - 1, "int");
            parms.Add(":END_INDEX", skip + take - 1, "int");
            return Query(sql, parms);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>

        #region GetCount

        public int GetDataByCountDetail_1(int year, int? month, int? week, int cmyId, string typeName)
        {
            var sql = @"SELECT
                          COUNT(1) AS R
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL1 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID1 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.C_PRONAME = :PRO_NAME";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":PRO_NAME", typeName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            var dt = Query(sql, parms);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public int GetDataByCountDetail_2(int year, int? month, int? week, int cmyId, string userType)
        {
            var sql = @"SELECT
                          COUNT(1)
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL2 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID2 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.C_USER_TYPE = :USER_TYPE";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":USER_TYPE", userType, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            var dt = Query(sql, parms);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public int GetDataByCountDetail_3(int year, int? month, int? week, int cmyId, string customerName)
        {
            var sql = @"SELECT
                          COUNT(1)
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL3 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID3 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_CUSTOMER_NAME = :CUSTOMER_NAME AND
                          T6.N_PROTYPE = 1";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":CUSTOMER_NAME", customerName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            var dt = Query(sql, parms);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public int GetDataByCountDetail_4(int year, int? month, int? week, int cmyId, string customerName)
        {
            var sql = @"SELECT
                          COUNT(1)
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL3 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID3 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_CUSTOMER_NAME = :CUSTOMER_NAME AND
                          T6.N_PROTYPE = 2";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":CUSTOMER_NAME", customerName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            var dt = Query(sql, parms);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public int GetDataByCountDetail_5(int year, int? month, int? week, int cmyId, string customerName, string projectName)
        {
            var sql = @"SELECT
                          COUNT(1)
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL3 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID3 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_CUSTOMER_NAME = :CUSTOMER_NAME AND
                          T6.N_PROTYPE = 3 AND
                          T6.C_PRONAME = :PRO_NAME";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":CUSTOMER_NAME", customerName, "string");
            parms.Add(":PRO_NAME", projectName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            var dt = Query(sql, parms);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public int GetDataByCountDetail_6(int year, int? month, int? week, int cmyId, string customerName, string projectName)
        {
            var sql = @"SELECT
                          COUNT(1)
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL3 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID3 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_CUSTOMER_NAME = :CUSTOMER_NAME AND
                          T6.N_PROTYPE = 4 AND
                          T6.C_PRONAME = :PRO_NAME";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":CUSTOMER_NAME", customerName, "string");
            parms.Add(":PRO_NAME", projectName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            var dt = Query(sql, parms);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        #endregion GetCount

        #region GetDataPage

        public DataTable GetDataByPage_1(int year, int? month, int? week, int cmyId, string typeName, string orderByStr, int skip, int take)
        {
            var sql = @"SELECT
                          ROW_NUMBER() OVER(ORDER BY T5." + orderByStr + @") AS R,
                          T5.*
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL1 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID1 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_PRONAME = :PRO_NAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":PRO_NAME", typeName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            sql = "SELECT T1.* FROM (" + sql + ") T1 WHERE T1.R>:START_INDEX AND T1.R<= :END_INDEX";
            parms.Add(":START_INDEX", skip, "int");
            parms.Add(":END_INDEX", skip + take, "int");
            return Query(sql, parms);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public DataTable GetDataByPage_2(int year, int? month, int? week, int cmyId, string userType, string orderByStr, int skip, int take)
        {
            var sql = @"SELECT
                          ROW_NUMBER() OVER(ORDER BY T5." + orderByStr + @") AS R,
                          T5.*
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL2 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID2 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_USER_TYPE = :USER_TYPE";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":USER_TYPE", userType, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            sql = "SELECT T1.* FROM (" + sql + ") T1 WHERE T1.R>:START_INDEX AND T1.R<= :END_INDEX";
            parms.Add(":START_INDEX", skip, "int");
            parms.Add(":END_INDEX", skip + take, "int");
            return Query(sql, parms);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public DataTable GetDataByPage_3(int year, int? month, int? week, int cmyId, string customerName, string orderByStr, int skip, int take)
        {
            var sql = @"SELECT
                          ROW_NUMBER() OVER(ORDER BY T5." + orderByStr + @") AS R,
                          T5.*
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL3 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID3 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_CUSTOMER_NAME = :CUSTOMER_NAME AND
                          T6.N_PROTYPE = 1";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":CUSTOMER_NAME", customerName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            sql = "SELECT T1.* FROM (" + sql + ") T1 WHERE T1.R>:START_INDEX AND T1.R<= :END_INDEX";
            parms.Add(":START_INDEX", skip, "int");
            parms.Add(":END_INDEX", skip + take, "int");
            return Query(sql, parms);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public DataTable GetDataByPage_4(int year, int? month, int? week, int cmyId, string customerName, string orderByStr, int skip, int take)
        {
            var sql = @"SELECT
                          ROW_NUMBER() OVER(ORDER BY T5." + orderByStr + @") AS R,
                          T5.*
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL3 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID3 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_CUSTOMER_NAME = :CUSTOMER_NAME AND
                          T6.N_PROTYPE = 2";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":CUSTOMER_NAME", customerName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            sql = "SELECT T1.* FROM (" + sql + ") T1 WHERE T1.R>:START_INDEX AND T1.R<= :END_INDEX";
            parms.Add(":START_INDEX", skip, "int");
            parms.Add(":END_INDEX", skip + take, "int");
            return Query(sql, parms);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public DataTable GetDataByPage_5(int year, int? month, int? week, int cmyId, string customerName, string projectName, string orderByStr, int skip, int take)
        {
            var sql = @"SELECT
                          ROW_NUMBER() OVER(ORDER BY T5." + orderByStr + @") AS R,
                          T5.*
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL3 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID3 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_CUSTOMER_NAME = :CUSTOMER_NAME AND
                          T6.N_PROTYPE = 3 AND
                          T6.C_PRONAME = :PRO_NAME";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":CUSTOMER_NAME", customerName, "string");
            parms.Add(":PRO_NAME", projectName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            sql = "SELECT T1.* FROM (" + sql + ") T1 WHERE T1.R>:START_INDEX AND T1.R<= :END_INDEX";
            parms.Add(":START_INDEX", skip, "int");
            parms.Add(":END_INDEX", skip + take, "int");
            return Query(sql, parms);
        }

        /// <summary>
        /// 根据年份,项目Id 获取银行流水总条数
        /// </summary>
        public DataTable GetDataByPage_6(int year, int? month, int? week, int cmyId, string customerName, string projectName, string orderByStr, int skip, int take)
        {
            var sql = @"SELECT
                          ROW_NUMBER() OVER(ORDER BY T5." + orderByStr + @") AS R,
                          T5.*
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN QS_PLAN_DETAIL3 T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID AND T6.N_ID3 = T5.N_APPLY_DETAILS_ID
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6.N_PLANAPP_ID= T3.N_WEEKAPP_ID AND
                          T6.C_CUSTOMER_NAME = :CUSTOMER_NAME AND
                          T6.N_PROTYPE = 4 AND
                          T6.C_PRONAME = :PRO_NAME";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", cmyId, "int");
            parms.Add(":CUSTOMER_NAME", customerName, "string");
            parms.Add(":PRO_NAME", projectName, "string");

            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                }
            }
            sql = "SELECT T1.* FROM (" + sql + ") T1 WHERE T1.R>:START_INDEX AND T1.R<= :END_INDEX";
            parms.Add(":START_INDEX", skip, "int");
            parms.Add(":END_INDEX", skip + take, "int");
            return Query(sql, parms);
        }

        #endregion GetDataPage

        public int GetDataByCountDetail(int typeKey, int year, int? month, int? week, int? proId, int cmyId, string parm1, string parm2)
        {
            switch (typeKey)
            {
                case 0:
                    return GetDataByCount(year, month, week, proId.Value, cmyId);

                case 1:
                    return GetDataByCountDetail_1(year, month, week, cmyId, parm1);

                case 2:
                    return GetDataByCountDetail_2(year, month, week, cmyId, parm1);

                case 3:
                    return GetDataByCountDetail_3(year, month, week, cmyId, parm1);

                case 4:
                    return GetDataByCountDetail_4(year, month, week, cmyId, parm1);

                case 5:
                    return GetDataByCountDetail_5(year, month, week, cmyId, parm1, parm2);

                case 6:
                    return GetDataByCountDetail_6(year, month, week, cmyId, parm1, parm2);

                default:
                    throw new Exception("类型不存在");
            }
        }

        public DataTable GetDataByPage(int typeKey, int year, int? month, int? week, int? proId, int cmyId, string parm1, string parm2, string orderBy, int skip, int take)
        {
            switch (typeKey)
            {
                case 0:
                    return GetDataByPage(year, month, week, proId.Value, cmyId, orderBy, skip, take);

                case 1:
                    return GetDataByPage_1(year, month, week, cmyId, parm1, orderBy, skip, take);

                case 2:
                    return GetDataByPage_2(year, month, week, cmyId, parm1, orderBy, skip, take);

                case 3:
                    return GetDataByPage_3(year, month, week, cmyId, parm1, orderBy, skip, take);

                case 4:
                    return GetDataByPage_4(year, month, week, cmyId, parm1, orderBy, skip, take);

                case 5:
                    return GetDataByPage_5(year, month, week, cmyId, parm1, parm2, orderBy, skip, take);

                case 6:
                    return GetDataByPage_6(year, month, week, cmyId, parm1, parm2, orderBy, skip, take);

                default:
                    throw new Exception("类型不存在");
            }
        }

        /// <summary>
        /// 根据年份 项目Id 查询总流水金额
        /// </summary>
        public decimal GetDataByDetail(int year, int? month, int? week, int proId, int companyId, int? nId, string DetailsCode, int? PLANAPP_ID)
        {
            var qs_plan_datail = "";
            var n_id = "";
            if (DetailsCode == "T1")
            {
                qs_plan_datail = "QS_PLAN_DETAIL1";
                n_id = "N_ID1";
            }
            else if (DetailsCode == "T2")
            {
                qs_plan_datail = "QS_PLAN_DETAIL2";
                n_id = "N_ID2";
            }
            else
            {
                qs_plan_datail = "QS_PLAN_DETAIL3";
                n_id = "N_ID3";
            }
            var sql = @"SELECT
                          ROUND(SUM(T5.N_AMT)/10000,6) AS AMT
                        FROM QX_PLANYEARAPPLY T1
                        INNER JOIN QS_PLANMONTHAPPLY T2 ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
                        INNER JOIN QS_PLANWEEKAPPLY T3 ON T2.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QS_PLANWEEK_DETAIL T4 ON T3.N_WEEKAPP_ID = T4.N_WEEKAPP_ID
                        INNER JOIN YX_BANK_DETAILLIST T5 ON T4.N_WEEKDETAIL_ID = T5.N_WEEKDETAIL_ID
                        INNER JOIN " + qs_plan_datail + @" T6 ON T4.n_Weekapp_Id = T6.n_Planapp_Id
                        WHERE
                          T1.N_BUSINESS_STATE = 2 AND
                          T2.N_BUSINESS_STATE = 2 AND
                          T3.N_BUSINESS_STATE = 2 AND
                          T5.N_STATE = 1 AND
                          T4.N_IF_DETAILS = 1 AND
                          T4.C_DETAILS_CODE = 'T1' AND
                          T6.N_TYPE = 3 AND
                          T5.N_COMPANY_ID = :COMPANY_ID AND
                          T5.N_PROID = :PRO_ID AND
                          T1.N_YEAR = :YEAR AND
                          T6." + n_id + @"=:nId AND
                          T4.C_DETAILS_CODE=:DetailsCode AND
                          T6.N_PLANAPP_ID=:PLANAPP_ID";

            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":PRO_ID", proId, "int");
            parms.Add(":YEAR", year, "int");
            parms.Add(":COMPANY_ID", companyId, "int");

            parms.Add(":nId", nId, "int");
            parms.Add(":DetailsCode", DetailsCode, "string");
            parms.Add(":PLANAPP_ID", PLANAPP_ID, "int");
            if (month != null)
            {
                sql += " AND T2.N_MONTH = :MONTH";
                parms.Add(":MONTH", month, "int");
                if (week != null)
                {
                    sql += " AND T3.N_WEEK = " + week;
                    //parms.Add(":ZWEEK_INDEX ", week, "int");
                }
            }
            var dt = Query(sql, parms);
            if (dt.Rows[0][0] == DBNull.Value)
            {
                return 0;
            }
            return Convert.ToDecimal(dt.Rows[0][0]);
        }

        public bool Refund(int bankListId, out string msg)
        {
            ServiceHelper myservice = new ServiceHelper();

            DataTable dt = new DataTable("orasptab");
            dt.Columns.Add("pname", Type.GetType("System.String"));
            dt.Columns.Add("pvalue", Type.GetType("System.String"));
            dt.Columns.Add("dir", Type.GetType("System.String"));
            dt.Columns.Add("size", Type.GetType("System.String"));

            dt.Rows.Add(new object[] { "bankId", bankListId, "1", 100 });
            //输出参数
            dt.Rows.Add(new object[] { "c_err", "", "0", 200 });
            msg = myservice.ExcuteProcEX("Pkg_Public_Method.Proc_Refund", dt, "Pkg_Public_Method", "Proc_Refund", "退款银行流水", 1);
            return msg == "退款成功" ? true : false;
        }

        public void GetBankBin(int companyId, string bankAccount, DateTime beginDate, DateTime overDate)
        {
            var app = new YQHL.GS.AppServer();
            var query = new YQHL.GS.Model.PageQuery();
            query.AccNo = bankAccount;
            query.BeginDate = beginDate;
            query.EndDate = overDate;
            query.MinAmt = 0;
            query.MaxAmt = 999999999;
            query.NextTag = "";
            query.ReqReserved1 = "";
            query.ReqReserved1 = "";
            var data = app.GetBankData(query);
            using (var conn = GetDbConn())
            {
                foreach (var item in data)
                {
                    //直接
                    var sql = @"SELECT
                                    COUNT(1) AS COUNT
                                FROM YX_BANK_DETAILLIST T1
                                WHERE
                                    T1.C_TRAN_FLOW = :C_TRAN_FLOW AND
                                    T1.N_COMPANY_ID = :N_COMPANY_ID AND
                                    T1.N_BANK_ID = 1";
                    var parms = ParmsHelper.GetParms();
                    parms.Add(":C_TRAN_FLOW", item["RepReserved4"].NewToObject<string>(), "string");
                    parms.Add(":N_COMPANY_ID", companyId, "int");
                    if (GetCount(sql, parms) > 0)
                    {
                        continue;
                    }

                    #region 新增

                    sql = @"INSERT INTO YX_BANK_DETAILLIST(
                                N_DETAIL_LISTID,N_LIST_ID,D_TRANDATE,
                                C_CRE_TYP,C_CRE_NO,N_AMT,N_AMT1,
                                N_FLAG1,C_ACCNO2,C_ACC_NAME1,N_FLAG2,
                                C_TRAN_FLOW,C_BFLOW,C_DET_NO,C_DET,C_RLTV_ACCNO,
                                C_CADBANK_NM,C_PROWEEKNUM,C_ACCNO1,N_BANK_ID,
                                N_STATE,N_PROID,N_WEEKDETAIL_ID,N_ID1,
                                C_DETAILS_CODE,D_BANKREDATE,N_COMPANY_ID,N_IF_DETAILS,
                                N_APPLY_DETAILS_ID,N_DETAILS_TYPE)
                            VALUES(
                                :N_DETAIL_LISTID,:N_LIST_ID,:D_TRANDATE,
                                :C_CRE_TYP,:C_CRE_NO,:N_AMT,:N_AMT1,
                                :N_FLAG1,:C_ACCNO2,:C_ACC_NAME1,:N_FLAG2,
                                :C_TRAN_FLOW,:C_BFLOW,:C_DET_NO,:C_DET,:C_RLTV_ACCNO,
                                :C_CADBANK_NM,:C_PROWEEKNUM,:C_ACCNO1,:N_BANK_ID,
                                :N_STATE,:N_PROID,:N_WEEKDETAIL_ID,:N_ID1,
                                :C_DETAILS_CODE,:D_BANKREDATE,:N_COMPANY_ID,:N_IF_DETAILS,
                                :N_APPLY_DETAILS_ID,:N_DETAILS_TYPE)";
                    parms.Clear();
                    parms.Add(":N_DETAIL_LISTID", GetSequence("SEQ_DETAIL_LISTID"), "int");
                    parms.Add(":N_LIST_ID", 2, "int");
                    parms.Add(":D_TRANDATE", DateTime.ParseExact(item["Date"].NewToObject<string>(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture), "datetime");
                    parms.Add(":C_CRE_TYP", item["VouhType"].NewToObject<string>(), "string");
                    parms.Add(":C_CRE_NO", item["VouhNo"].NewToObject<string>(), "string");
                    if (item["Drcrf"].NewToObject<int>() == 1)//1:借 2:贷
                    {
                        parms.Add(":N_AMT", item["DebitAmount"].NewToObject<decimal>() / 100, "decimal");
                    }
                    else
                    {
                        parms.Add(":N_AMT", item["CreditAmount"].NewToObject<decimal>() / 100, "decimal");
                    }
                    parms.Add(":N_AMT1", item["Balance"].NewToObject<decimal>(), "decimal");
                    parms.Add(":N_FLAG1", item["Drcrf"].NewToObject<int>() == 1 ? 0 : 1, "int");
                    parms.Add(":C_ACCNO2", item["RecipAccNo"].NewToObject<string>(), "string");
                    parms.Add(":C_ACC_NAME1", item["RecipName"].NewToObject<string>(), "string");
                    //*交易钞汇标志 0－钞户 1－汇户
                    parms.Add(":N_FLAG2", null, "int");
                    parms.Add(":C_TRAN_FLOW", item["RepReserved4"].NewToObject<string>(), "string");
                    parms.Add(":C_BFLOW", null, "string");
                    parms.Add(":C_DET_NO", null, "string");
                    parms.Add(":C_DET", item["Summary"].NewToObject<string>(), "string");
                    parms.Add(":C_RLTV_ACCNO", null, "string");
                    parms.Add(":C_CADBANK_NM", item["RecipBkName"].NewToObject<string>(), "string");
                    parms.Add(":C_PROWEEKNUM", null, "string");
                    parms.Add(":C_ACCNO1", bankAccount, "string");
                    parms.Add(":N_BANK_ID", 1, "int");
                    parms.Add(":N_STATE", 0, "int");
                    parms.Add(":N_PROID", null, "int");
                    parms.Add(":N_WEEKDETAIL_ID", null, "int");
                    parms.Add(":N_ID1", null, "int");
                    parms.Add(":C_DETAILS_CODE", null, "string");
                    parms.Add(":D_BANKREDATE", DateTime.ParseExact(item["Date"].NewToObject<string>(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture), "datetime");
                    parms.Add(":N_COMPANY_ID", companyId, "int");
                    parms.Add(":N_IF_DETAILS", 0, "int");
                    parms.Add(":N_APPLY_DETAILS_ID", null, "int");
                    parms.Add(":N_DETAILS_TYPE", null, "int");
                    NoQuery(sql, parms);

                    #endregion 新增
                }
            }
        }

        public void AppGo()
        {
            var date = DateTime.Now;
            var parms = ParmsHelper.GetParms();
            var sql = @"SELECT
                            T1.*
                        FROM YX_ACCOUNT T1
                        WHERE
                            T1.STATE =1";
            var dt = Query(sql);
            foreach (var item in dt.AsEnumerable())
            {
                try
                {
                    new YX_BANK_LISTBLL().GetBankBin(
                    item.GetValue<int>("COMPANY_ID"),
                    item.GetValue<string>("ACCOUNTNO"),
                    DateTime.Now.AddDays(-1),
                    DateTime.Now
                    );
                }
                catch (Exception ex)
                {
                    //LOG
                    sql = "INSERT INTO YX_BANK_DLOG(STATUS,ID,OPEAR_DATE,OPEAR_ID,OPEAR_NAME,LOG_TEXT) VALUES(:STATUS,:ID,:OPEAR_DATE,:OPEAR_ID,:OPEAR_NAME,:LOG_TEXT)";
                    parms.Clear();
                    parms.Add(":ID", Guid.NewGuid().ToString(), "string");
                    parms.Add(":OPEAR_DATE", DateTime.Now, "datetime");
                    parms.Add(":OPEAR_ID", null, "int");
                    parms.Add(":OPEAR_NAME", "系统添加", "string");
                    parms.Add(":LOG_TEXT", "MSG:" + ex.Message + "。Entity:" + item.ToJson() + "。" + ex.TargetSite, "string");
                    parms.Add(":STATUS", 0, "int");
                    NoQuery(sql, parms);
                }
            }
            sql = "INSERT INTO YX_BANK_DLOG(STATUS,ID,OPEAR_DATE,OPEAR_ID,OPEAR_NAME,LOG_TEXT) VALUES(:STATUS,:ID,:OPEAR_DATE,:OPEAR_ID,:OPEAR_NAME,:LOG_TEXT)";
            parms.Clear();
            parms.Add(":ID", Guid.NewGuid().ToString(), "string");
            parms.Add(":OPEAR_DATE", DateTime.Now, "datetime");
            parms.Add(":OPEAR_ID", null, "int");
            parms.Add(":OPEAR_NAME", "系统添加", "string");
            parms.Add(":LOG_TEXT", "执行完一次银行抓取任务工耗时耗时：" + (DateTime.Now - date).Seconds + "秒", "string");
            parms.Add(":STATUS", 1, "int");
            NoQuery(sql, parms);
            //执行一次银行匹配
            ProBankSet();
        }

        #endregion 子项目详情银行流水
    }
}